package com.A1908.dao.impl;

import com.A1908.dao.AttendPeopleInfoDao;
import com.A1908.entity.AttendPeopleInfo;
import com.A1908.entity.FeastInfo;
import com.A1908.entity.PageEntity;
import com.A1908.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AttendPeopleInfoDaoImpl extends BaseDao implements AttendPeopleInfoDao {
    DaoUtil daoUtil = new DaoUtil();

    @Override
    public int addAttendPeople(AttendPeopleInfo attendPeopleInfo) {
        /*insert into attendpeopleinfo values (default,'test','2021-04-25',1,1000,'无',1)*/
        String sql = "INSERT INTO `attendpeopleinfo` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?);";
        Object[] objs = {attendPeopleInfo.getAttendPeople(), attendPeopleInfo.getAttendDate(), attendPeopleInfo.getFeastID(), attendPeopleInfo.getGift(), attendPeopleInfo.getNote(), attendPeopleInfo.getRelationID()};
        int result = super.executeUpdate(sql, objs);
        return result;
    }

    @Override
    public int delAttendPeople(AttendPeopleInfo attendPeopleInfo) {
        String sql = "delete from attendpeopleinfo where id = ?";
        Object[] objects = {attendPeopleInfo.getId()};
        return executeUpdate(sql,objects);
    }

    @Override
    public int delAttendPeopleByFeast(FeastInfo info) {
        String sql = "delete from attendpeopleinfo where attendFeast = ?;";
        Object[] objects = {info.getId()};
        return executeUpdate(sql,objects);
    }

    @Override
    public List<AttendPeopleInfo> getAllAttendPeopleInfo(User user) {
        String sql = "SELECT\t\n" +
                "\tfeastinfo.feastName,\t\n" +
                "\tattendpeopleinfo.*\t\n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER JOIN attendpeopleinfo ON attendpeopleinfo.attendFeast = feastinfo.id\n" +
                "\tINNER JOIN `user` ON `user`.id = feastinfo.feastUser \n" +
                "WHERE\n" +
                "\t`user`.id = ?;\n";
        Object[] objects = {user.getId()};
        Connection conn = super.conn();
        PreparedStatement pstmt = super.pstmt(conn, sql, objects);
        try {
            ResultSet rs = pstmt.executeQuery();
            AttendPeopleInfo atInfo = null;
            List<AttendPeopleInfo> list = new ArrayList<>();
            while (rs.next()) {
                atInfo = new AttendPeopleInfo();
                atInfo.setFeastName(rs.getString(1));
                atInfo.setId(rs.getInt(2));
                atInfo.setAttendPeople(rs.getString(3));
                atInfo.setAttendDate(rs.getString(4));
                atInfo.setFeastID(rs.getInt(5));
                atInfo.setGift(rs.getInt(6));
                atInfo.setNote(rs.getString(7));
                list.add(atInfo);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<AttendPeopleInfo> getAllAttendPeopleInfo(User user, PageEntity pageEntity) {
        String sql = "SELECT\n" +
                "\tfeastinfo.feastName,\t\n" +
                "\tattendpeopleinfo.*\n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER\n" +
                "\tJOIN attendpeopleinfo ON attendpeopleinfo.attendFeast = feastinfo.id\n" +
                "\tINNER\n" +
                "\tJOIN `user` ON `user`.id = feastinfo.feastUser\n" +
                "WHERE\t\n" +
                "\t`user`.id = ?\n" +
                "\tLIMIT ?,?;";
        int startNum = (pageEntity.getCurrentPage() - 1) * pageEntity.getPageSize();
        Object[] objects = {user.getId(), startNum, pageEntity.getPageSize()};
        Connection conn = super.conn();
        PreparedStatement pstmt = super.pstmt(conn, sql, objects);
        try {
            ResultSet rs = pstmt.executeQuery();
            AttendPeopleInfo atInfo = null;
            List<AttendPeopleInfo> list = new ArrayList<>();
            while (rs.next()) {
                atInfo = new AttendPeopleInfo();
                atInfo.setFeastName(rs.getString(1));
                atInfo.setId(rs.getInt(2));
                atInfo.setAttendPeople(rs.getString(3));
                atInfo.setAttendDate(rs.getString(4));
                atInfo.setFeastID(rs.getInt(5));
                atInfo.setGift(rs.getInt(6));
                atInfo.setNote(rs.getString(7));
                list.add(atInfo);
            }
            pageEntity.setTotalCount(getCount(user));
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<AttendPeopleInfo> getAttendPeopleInfoByFeast(FeastInfo feastInfo) {
        String sql = "SELECT a.id,a.attendPeople,a.attendDate,f.feastName,a.gift,a.note,r.relationType,a.attendFeast,a.relationID FROM attendpeopleinfo a \n" +
                "INNER JOIN feastinfo f ON f.id = a.attendFeast\n" +
                "INNER JOIN relationtype r ON r.id = a.relationID\n" +
                "WHERE a.attendFeast = ?";

        Object[] objects = {feastInfo.getId()};
        Connection connection = conn();
        PreparedStatement pstmt = pstmt(connection, sql, objects);
        List<AttendPeopleInfo> list = new ArrayList<AttendPeopleInfo>();
        try {
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                AttendPeopleInfo attendPeopleInfo = new AttendPeopleInfo();
                attendPeopleInfo.setId(rs.getInt(1));
                attendPeopleInfo.setAttendPeople(rs.getString(2));
                attendPeopleInfo.setAttendDate(rs.getString(3));
                attendPeopleInfo.setFeastName(rs.getString(4));
                attendPeopleInfo.setGift(rs.getDouble(5));
                attendPeopleInfo.setNote(rs.getString(6));
                attendPeopleInfo.setRelationType(rs.getString(7));
                attendPeopleInfo.setFeastID(rs.getInt(8));
                attendPeopleInfo.setRelationID(rs.getInt(9));
                list.add(attendPeopleInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public double getTotalGiftByByFeast(FeastInfo feastInfo) {
        Connection connection = conn();
        String sql = "select sum(gift) from attendpeopleinfo where attendFeast = ?;";
        Object[] objects = {feastInfo.getId()};
        PreparedStatement pstmt = pstmt(connection, sql, objects);
        ResultSet rs = null;
        try {
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getDouble(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            cloesAll(connection, pstmt, rs);
        }
        return 0;
    }

    @Override
    public int editAttendPeople(AttendPeopleInfo attendPeopleInfo) {
        String sql = "update attendpeopleinfo set attendPeople = ?,attendDate = ?,attendFeast = ?,\n" +
                "gift = ?,note = ?,relationID = ? where id = ?";
        Object[] objects = {
                attendPeopleInfo.getAttendPeople(),
                attendPeopleInfo.getAttendDate(),
                attendPeopleInfo.getFeastID(),
                attendPeopleInfo.getGift(),
                attendPeopleInfo.getNote(),
                attendPeopleInfo.getRelationID(),
                attendPeopleInfo.getId()
        };
        return executeUpdate(sql,objects);
    }

    @Override
    public int editSingleValue(int id, String field, String value) {
        String sql = "update attendpeopleinfo set " + field + " = ? where id = ?";
        Object[] objects = {value, id};
        return executeUpdate(sql, objects);
    }

    @Override
    public int getCount(User user) {
        String sql = "SELECT\n" +
                "\tCOUNT(*) \n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER JOIN attendpeopleinfo ON attendpeopleinfo.attendFeast = feastinfo.id\n" +
                "\tINNER JOIN `user` ON `user`.id = feastinfo.feastUser \n" +
                "WHERE\n" +
                "\t`user`.id = ?;";
        return daoUtil.getCount(sql, user);
    }
}
